package com.example.exercise.MysqlUtil;



import com.example.exercise.Base.CountBase;
import com.example.exercise.Base.SignBase;
import com.example.exercise.Base.User;
import com.mysql.jdbc.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

public class DBService {

    private static Connection conn=null;
    private static PreparedStatement ps=null;//操作整合sql语句的对象
    private static ResultSet rs=null;//查询结果的集合

    //DBService 对象
    public static DBService dbService=null;

    /**
     * 构造方法 私有化
     * */

    private DBService(){

    }

    /**
     * 获取MySQL数据库单例类对象
     * */

    public static DBService getDbService(){
        if(dbService==null){
            dbService=new DBService();
        }
        return dbService;
    }



    public static List<User> getUserCard(String Name,String ClassRoom){
        //结果存放集合
        List<User> list=new ArrayList<User>();
        //MySQL 语句
        String sql="select * from card where Name ='"+Name+"' and CLASS ='"+ClassRoom +"';";
        //获取链接数据库对象
        conn= Mysqlconnect.get().getConnection();

        try {
            if(conn!=null&&(!conn.isClosed())){
                ps= (PreparedStatement) conn.prepareStatement(sql);
                if(ps!=null){
                    rs= ps.executeQuery();
                    if(rs!=null){
                        while(rs.next()){
                            User u=new User();
                            u.setName(rs.getString("Name"));
                            u.setClassroom(rs.getString("CLASS"));
                            u.setCardid(rs.getString("Card"));
                            u.setGuashi(rs.getString("是否挂失"));
                            list.add(u);
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Mysqlconnect.closeAll(conn,ps,rs);//关闭相关操作
        return list;
    }


    public  static List<SignBase> getUsernoinsertSign(String name){
        //结果存放集合
        List<SignBase> list=new ArrayList<SignBase>();
        //MySQL 语句
        String sql="select TIME,Name,CLASS,是否签到 from card where Name ='"+name+"' and Card = '无卡';";
        //获取链接数据库对象
        conn= Mysqlconnect.get().getConnection();
        SignBase signBase = new SignBase();
        try {
            if(conn!=null&&(!conn.isClosed())){
                ps= (PreparedStatement) conn.prepareStatement(sql);
                if(ps!=null){
                    rs= ps.executeQuery();
                    if(rs!=null){
                        while(rs.next()){
                            signBase.setTime(rs.getString("TIME"));
                            signBase.setName(rs.getString("NAME"));
                            signBase.setClassroom(rs.getString("CLASS"));
                            signBase.setIfsign(rs.getString("是否签到"));

                            list.add(signBase);
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Mysqlconnect.closeAll(conn,ps,rs);//关闭相关操作
        return list;
    }

    public  static List<SignBase> getUserSign(String name){
        //结果存放集合
        List<SignBase> list=new ArrayList<SignBase>();
        //MySQL 语句
        String sql="select TIME,Name,CLASS,是否签到 from card where Name ='"+name+"';";
        //获取链接数据库对象
        conn= Mysqlconnect.get().getConnection();
        SignBase signBase = new SignBase();
        try {
            if(conn!=null&&(!conn.isClosed())){
                ps= (PreparedStatement) conn.prepareStatement(sql);
                if(ps!=null){
                    rs= ps.executeQuery();
                    if(rs!=null){
                        while(rs.next()){
                            signBase.setTime(rs.getString("TIME"));
                            signBase.setName(rs.getString("NAME"));
                            signBase.setClassroom(rs.getString("CLASS"));
                            signBase.setIfsign(rs.getString("是否签到"));
                            list.add(signBase);
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Mysqlconnect.closeAll(conn,ps,rs);//关闭相关操作
        return list;
    }

    public static String getMessage(){
        //结果存放集合
        String Message = "";
        //MySQL 语句
        String sql="select Message1 from information;";
        //获取链接数据库对象
        conn= Mysqlconnect.get().getConnection();
        try {
            if(conn!=null&&(!conn.isClosed())){
                ps= (PreparedStatement) conn.prepareStatement(sql);
                if(ps!=null){
                    rs= ps.executeQuery();
                    if(rs!=null){
                        while(rs.next()){
                            Message =  rs.getString("Message1");
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Mysqlconnect.closeAll(conn,ps,rs);//关闭相关操作
        return Message;
    }

    public static String getVersion(){
        //结果存放集合
       String version = null;
        //MySQL 语句
        String sql="select version from information;";
        //获取链接数据库对象
        conn= Mysqlconnect.get().getConnection();
        try {
            if(conn!=null&&(!conn.isClosed())){
                ps= (PreparedStatement) conn.prepareStatement(sql);
                if(ps!=null){
                    rs= ps.executeQuery();
                    if(rs!=null){
                        while(rs.next()){
                           version =  rs.getString("version");
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Mysqlconnect.closeAll(conn,ps,rs);//关闭相关操作
        return version;
    }

    public static List<User> getUserSign(){
        //结果存放集合
        List<User> list=new ArrayList<User>();
        //MySQL 语句
        String sql="select NAME,CLASS from card where TIME = 0 AND 是否乐队 = '否';";
        //获取链接数据库对象
        conn= Mysqlconnect.get().getConnection();
        try {
            if(conn!=null&&(!conn.isClosed())){
                ps= (PreparedStatement) conn.prepareStatement(sql);
                if(ps!=null){
                    rs= ps.executeQuery();
                    if(rs!=null){
                        while(rs.next()){
                            User u=new User();
                            u.setName(rs.getString("Name"));
                            u.setClassroom(rs.getString("CLASS"));
                            list.add(u);
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Mysqlconnect.closeAll(conn,ps,rs);//关闭相关操作
        return list;
    }
    public static List<User> getUserName(String CARD){
        //结果存放集合
        List<User> list=new ArrayList<User>();
        //MySQL 语句
        String sql="select * from card where Card ='"+CARD+"';";
        //获取链接数据库对象
        conn= Mysqlconnect.get().getConnection();
        try {
            if(conn!=null&&(!conn.isClosed())){
                ps= (PreparedStatement) conn.prepareStatement(sql);
                if(ps!=null){
                    rs= ps.executeQuery();
                    if(rs!=null){
                        while(rs.next()){
                            User u=new User();
                            u.setName(rs.getString("Name"));
                            u.setClassroom(rs.getString("CLASS"));
                            u.setCardid(rs.getString("Card"));
                            list.add(u);
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Mysqlconnect.closeAll(conn,ps,rs);//关闭相关操作
        return list;
    }

    public static List<CountBase> getCount(){
        //结果存放集合
        List<CountBase> list=new ArrayList<CountBase>();
        //MySQL 语句
        String sql="select CLASS,COUNT(*)AS 数量 from card where TIME = 0 AND `是否乐队` = '否' GROUP BY CLASS;";
        //获取链接数据库对象
        conn= Mysqlconnect.get().getConnection();
        try {
            if(conn!=null&&(!conn.isClosed())){
                ps= (PreparedStatement) conn.prepareStatement(sql);
                if(ps!=null){
                    rs= ps.executeQuery();
                    if(rs!=null){
                        while(rs.next()){
                            CountBase u=new CountBase();
                            u.setClassroom(rs.getString("CLASS"));
                            u.setCount(rs.getString("数量"));
                            list.add(u);
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Mysqlconnect.closeAll(conn,ps,rs);//关闭相关操作
        return list;
    }
    public static int getUser(String Name){
        //结果存放集合
        int result = -1;
        //MySQL 语句
        String sql="select Name from card where Name ='"+Name+"';";
        //获取链接数据库对象
        conn= Mysqlconnect.get().getConnection();
        try {
            if(conn!=null&&(!conn.isClosed())){
                ps= (PreparedStatement) conn.prepareStatement(sql);
                if(ps!=null){
                    rs= ps.executeQuery();
                    if(rs!=null){
                        while(rs.next()){
                            if (rs.getString("Name").equals(Name)){
                                result = 1;
                                break;
                            }
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Mysqlconnect.closeAll(conn,ps,rs);//关闭相关操作
        return result;
    }

    public static int getUserMusic(String Name){
        //结果存放集合
       int result = 1;
        //MySQL 语句
        String sql="select 是否乐队 from card where Name ='"+Name+"';";
        //获取链接数据库对象
        conn= Mysqlconnect.get().getConnection();
        try {
            if(conn!=null&&(!conn.isClosed())){
                ps= (PreparedStatement) conn.prepareStatement(sql);
                if(ps!=null){
                    rs= ps.executeQuery();
                    if(rs!=null){
                        while(rs.next()){
                            if (rs.getString("是否乐队").equals("否")){
                                result = 0;
                            }
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Mysqlconnect.closeAll(conn,ps,rs);//关闭相关操作
        return result;
    }

    public  static  int UpdateUserCard(String Name,String Classroom,String state,String Cardid){
        int result=-1;
        if(!StringUtils.isNullOrEmpty(Name)){
            //获取链接数据库对象
            try {
                conn= Mysqlconnect.get().getConnection();
            } catch (Exception e) {
                e.printStackTrace();
            }
            //MySQL 语句
            String sql="update card set CARD = ? , 是否挂失 = ? where Name=? and Class = ? ";
            try {
                boolean closed=conn.isClosed();
                if(conn!=null&&(!closed)){
                    ps= (PreparedStatement) conn.prepareStatement(sql);
                    ps.setString(1,Cardid);//第一个参数state 一定要和上面SQL语句字段顺序一致
                    ps.setString(2,state);//第一个参数state 一定要和上面SQL语句字段顺序一致
                    ps.setString(3,Name);//第二个参数 phone 一定要和上面SQL语句字段顺序一致
                    ps.setString(4,Classroom);//第二个参数 phone 一定要和上面SQL语句字段顺序一致
                    result=ps.executeUpdate();//返回1 执行成功
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        Mysqlconnect.closeAll(conn,ps);//关闭相关操作
        return result;
    }

    public  static  int Clear(){
        int result=-1;

            //获取链接数据库对象
            try {
                conn= Mysqlconnect.get().getConnection();
            } catch (Exception e) {
                e.printStackTrace();
            }
            //MySQL 语句
            String sql="update card set 是否签到 = ? , TIME = ?,无卡签到 = ?";
            try {
                boolean closed=conn.isClosed();
                if(conn!=null&&(!closed)){
                    ps= (PreparedStatement) conn.prepareStatement(sql);
                    ps.setString(1,"今日无记录");//第一个参数state 一定要和上面SQL语句字段顺序一致
                    ps.setString(2,"0");//第一个参数state 一定要和上面SQL语句字段顺序一致
                    ps.setString(3,"今日无记录");//第二个参数 phone 一定要和上面SQL语句字段顺序一致
                    ps.executeUpdate();
                    result=1;//返回1 执行成功
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        Mysqlconnect.closeAll(conn,ps);//关闭相关操作
        return result;
    }


    public  static  int LoseState(String Name,String Classroom,String state){
        int result=-1;
        if(!StringUtils.isNullOrEmpty(Name)){
            //获取链接数据库对象
            try {
                conn= Mysqlconnect.get().getConnection();
            } catch (Exception e) {
                e.printStackTrace();
            }
            //MySQL 语句
            String sql="update card set 是否挂失=? where Name=? and Class = ? ";
            try {
                boolean closed=conn.isClosed();
                if(conn!=null&&(!closed)){
                    ps= (PreparedStatement) conn.prepareStatement(sql);
                    ps.setString(1,state);//第一个参数state 一定要和上面SQL语句字段顺序一致
                    ps.setString(2,Name);//第二个参数 phone 一定要和上面SQL语句字段顺序一致
                    ps.setString(3,Classroom);//第二个参数 phone 一定要和上面SQL语句字段顺序一致
                    result=ps.executeUpdate();//返回1 执行成功
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        Mysqlconnect.closeAll(conn,ps);//关闭相关操作
        return result;
    }

    public  static  int update1(List<SignBase> list,String state, String Name){
        int result=-1;
        if(!StringUtils.isNullOrEmpty(Name)){
            //获取链接数据库对象
            try {
                conn= Mysqlconnect.get().getConnection();
            } catch (Exception e) {
                e.printStackTrace();
            }
            //MySQL 语句
            String sql="update card set 是否签到=?,TIME=?,无卡签到=? where Name=?";
            try {
                boolean closed=conn.isClosed();
                if(conn!=null&&(!closed)){
                    for(SignBase signBase:list){
                        ps= (PreparedStatement) conn.prepareStatement(sql);
                        String ifsign=signBase.getIfsign();
                        String TIME=signBase.getTime();
                        ps.setString(1,ifsign);
                        ps.setString(2,TIME);
                        ps.setString(3,state);
                        ps.setString(4,Name.trim());
                        result=ps.executeUpdate();
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        Mysqlconnect.closeAll(conn,ps);//关闭相关操作
        return result;
    }

    public  static  int update2(List<SignBase> list,String state, String Name){
        int result=-1;
        if(!StringUtils.isNullOrEmpty(Name)){
            //获取链接数据库对象
            try {
                conn= Mysqlconnect.get().getConnection();
            } catch (Exception e) {
                e.printStackTrace();
            }
            //MySQL 语句
            String sql="insert into card (Card,Name,是否挂失,CLASS,是否签到,TIME,无卡签到) values(?,?,?,?,?,?,?)";
            try {
                boolean closed=conn.isClosed();
                if(conn!=null&&(!closed)){
                    for(SignBase signBase:list){
                        ps= (PreparedStatement) conn.prepareStatement(sql);
                        String ifsign=signBase.getIfsign();
                        String TIME=signBase.getTime();
                        String classroom = signBase.getClassroom();
                        ps.setString(1,"无卡");
                        ps.setString(2,Name);
                        ps.setString(3,"无卡");
                        ps.setString(4,classroom);
                        ps.setString(5,ifsign);//第一个参数 name 规则同上
                        ps.setString(6,TIME);//第二个参数 phone 规则同上
                        ps.setString(7,state);//第三个参数 content 规则同上
                        ps.executeUpdate();//返回1 执行成功
                        result=1;
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        Mysqlconnect.closeAll(conn,ps);//关闭相关操作
        return result;
    }

    public  static  int updatemusic(String Name){
        int result=-1;
        if(!StringUtils.isNullOrEmpty(Name)){
            //获取链接数据库对象
            try {
                conn= Mysqlconnect.get().getConnection();
            } catch (Exception e) {
                e.printStackTrace();
            }
            //MySQL 语句
            String sql="update card set 是否乐队='否' where Name= '"+Name +"'";
            try {
                boolean closed=conn.isClosed();
                if(conn!=null&&(!closed)){
                        ps= (PreparedStatement) conn.prepareStatement(sql);
                        result=ps.executeUpdate();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        Mysqlconnect.closeAll(conn,ps);//关闭相关操作
        return result;
    }

    public  static  int updatenomusic(String Name){
        int result=-1;
        if(!StringUtils.isNullOrEmpty(Name)){
            //获取链接数据库对象
            try {
                conn= Mysqlconnect.get().getConnection();
            } catch (Exception e) {
                e.printStackTrace();
            }
            //MySQL 语句
            String sql="update card set 是否乐队='是' where Name= '"+Name +"'";
            try {
                boolean closed=conn.isClosed();
                if(conn!=null&&(!closed)){
                    ps= (PreparedStatement) conn.prepareStatement(sql);
                    result=ps.executeUpdate();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        Mysqlconnect.closeAll(conn,ps);//关闭相关操作
        return result;
    }
    /**
     * 修改数据库中某个对象的状态  改
     * */

//    public int updateUserData(String phone){
//        int result=-1;
//        if(!StringUtils.isEmpty(phone)){
//            //获取链接数据库对象
//            conn= DBOpenHelper.getConn();
//            //MySQL 语句
//            String sql="update user set state=? where phone=?";
//            try {
//                boolean closed=conn.isClosed();
//                if(conn!=null&&(!closed)){
//                    ps= (PreparedStatement) conn.prepareStatement(sql);
//                    ps.setString(1,"1");//第一个参数state 一定要和上面SQL语句字段顺序一致
//                    ps.setString(2,phone);//第二个参数 phone 一定要和上面SQL语句字段顺序一致
//                    result=ps.executeUpdate();//返回1 执行成功
//                }
//            } catch (SQLException e) {
//                e.printStackTrace();
//            }
//        }
//        DBOpenHelper.closeAll(conn,ps);//关闭相关操作
//        return result;
//    }

    /**
     * 批量向数据库插入数据  增
     *
     * @return*/

    //获取密码验证
    public static String[] getAuth(String Acc){
        //结果存放集合
        String[] Auth = new String[2];

        Auth[0]  = Acc;


        //MySQL 语句
        String sql="select * from auth where Acc = '"+Acc + "';";
        //获取链接数据库对象
        conn= Mysqlconnect.get().getConnection();
        try {
            if(conn!=null&&(!conn.isClosed())){
                ps= (PreparedStatement) conn.prepareStatement(sql);
                if(ps!=null){
                    rs= ps.executeQuery();
                    if(rs!=null){
                        while(rs.next()){
                            Auth[1]=  rs.getString("Pwd");
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Mysqlconnect.closeAll(conn,ps,rs);//关闭相关操作
        return Auth;
    }
    public static int getUserMa(){

        int result = 0;
        //MySQL 语句
        String sql="select * from auth where Acc = 'ma';";
        //获取链接数据库对象
        conn= Mysqlconnect.get().getConnection();
        try {
            if(conn!=null&&(!conn.isClosed())){
                ps= (PreparedStatement) conn.prepareStatement(sql);
                if(ps!=null){
                    rs= ps.executeQuery();
                    if(rs!=null){
                        while(rs.next()){
                            String result1;
                            result1  = rs.getString("Pwd");
                            result = Integer.parseInt(result1);

                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Mysqlconnect.closeAll(conn,ps,rs);//关闭相关操作
        return result;
    }

    public  static  int SetUserMa(int Ma){
        int result=-1;
            //获取链接数据库对象
            try {
                conn= Mysqlconnect.get().getConnection();
            } catch (Exception e) {
                e.printStackTrace();
            }
            //MySQL 语句
            String sql="update auth set Pwd='"+Ma+"' where Acc= 'ma'";
            try {
                boolean closed=conn.isClosed();
                if(conn!=null&&(!closed)){
                    ps= (PreparedStatement) conn.prepareStatement(sql);
                    result=ps.executeUpdate();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        Mysqlconnect.closeAll(conn,ps);//关闭相关操作
        return result;
    }


    public static int insertClassData(String classname){
        int result=-1;
            //获取链接数据库对象
            conn= Mysqlconnect.get().getConnection();
            //MySQL 语句
            String sql="INSERT INTO classroom (班级) VALUES (?)";
            try {
                if((conn!=null)&&(!conn.isClosed())){
                        ps= (PreparedStatement) conn.prepareStatement(sql);
                        ps.setString(1,classname);//第一个参数 name 规则同上
                        result=ps.executeUpdate();//返回1 执行成功
                    }
                }


            catch (SQLException e) {
                System.out.println("111111111111111111113");
                e.printStackTrace();
            }

        Mysqlconnect.closeAll(conn,ps);//关闭相关操作
        return result;
    }
    public static int insertUserData(List<User> list){
        int result=-1;
        if((list!=null)&&(list.size()>0)){
            //获取链接数据库对象
            conn= Mysqlconnect.get().getConnection();
            //MySQL 语句
            String sql="INSERT INTO card (Card,Name,Class) VALUES (?,?,?)";
            try {
                System.out.println("11111111111111111111");
             //   boolean closed=conn.isClosed();
                if((conn!=null)&&(!conn.isClosed())){
                    System.out.println("111111111111111111112");
                    for(User user:list){
                        ps= (PreparedStatement) conn.prepareStatement(sql);
                        String name=user.getName();
                        String Cardid=user.getCardid();
                        String Classrooom=user.getClassroom();
                        ps.setString(1,Cardid);//第一个参数 name 规则同上
                        ps.setString(2,name);//第二个参数 phone 规则同上
                        ps.setString(3,Classrooom);//第三个参数 content 规则同上
                      //  ps.setString(3,Classrooom);//第三个参数 content 规则同上
                       // ps.setString(4,state);//第四个参数 state 规则同上
                        result=ps.executeUpdate();//返回1 执行成功
                    }
                }


            } catch (SQLException e) {
                System.out.println("111111111111111111113");
                e.printStackTrace();
            }
        }
        Mysqlconnect.closeAll(conn,ps);//关闭相关操作
        return result;
    }


    /**
     * 删除数据 删
     * */

    public static int delUserData(String cardid){
        int result=-1;

            //获取链接数据库对象
            conn= Mysqlconnect.get().getConnection();
            //MySQL 语句
            String sql="delete from card where Card= ?";
            try {
                boolean closed=conn.isClosed();
                if((conn!=null)&&(!closed)){
                    ps= (PreparedStatement) conn.prepareStatement(sql);
                    ps.setString(1, cardid);
                    ps.executeUpdate();
                    result=1;//返回1 执行成功
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        Mysqlconnect.closeAll(conn,ps);//关闭相关操作
        return result;
    }
    public static int delUserDataWithName(String name){
        int result=-1;

        //获取链接数据库对象
        conn= Mysqlconnect.get().getConnection();
        //MySQL 语句
        String sql="delete from card where Card= ?";
        try {
            boolean closed=conn.isClosed();
            if((conn!=null)&&(!closed)){
                ps= (PreparedStatement) conn.prepareStatement(sql);
                ps.setString(1, name);
                ps.executeUpdate();
                result=1;//返回1 执行成功
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        Mysqlconnect.closeAll(conn,ps);//关闭相关操作
        return result;
    }

}
